package com.hy.dao;

import com.hy.bean.User;
import com.hy.utils.DBManager;
import com.hy.utils.DateUtil;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.util.List;
import java.util.Map;

public class MessageDao {
    private QueryRunner qr = new QueryRunner();

    public List<Map<String, Object>> selectMessage(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        try {
            String setRowNumberSQL = "SET @row_number = 0;";
            qr.update(conn, setRowNumberSQL);

            String setCurrentUserSQL = "SET @current_user = NULL;";
            qr.update(conn, setCurrentUserSQL);

            String mainSQL = "SELECT result.user, user_info.user_name, user_info.img, " +
                    "result.content, result.create_time " +
                    "FROM ( " +
                    "    SELECT user, content, create_time, " +
                    "        @row_number := CASE WHEN @current_user = user THEN @row_number + 1 ELSE 1 END AS rn, " +
                    "        @current_user := user " +
                    "    FROM ( " +
                    "        SELECT user_id AS user, content, create_time " +
                    "        FROM ( " +
                    "            SELECT user_id, user1_id AS sender_id, content, create_time " +
                    "            FROM message WHERE user1_id = ? " +
                    "            UNION ALL " +
                    "            SELECT user1_id, user_id AS sender_id, content, create_time " +
                    "            FROM message WHERE user_id = ? " +
                    "        ) AS subquery " +
                    "        ORDER BY user_id, create_time DESC " +
                    "    ) AS ranked_messages " +
                    ") AS result " +
                    "JOIN ums_user AS user_info ON user_info.id = result.user " +
                    "WHERE rn = 1 ORDER BY result.create_time DESC;";

            List<Map<String, Object>> query = qr.query(conn, mainSQL, new MapListHandler(), id, id);
            return query;
        } finally {
            DbUtils.close(conn);
        }
    }

    public List<Map<String, Object>> selectUserMessage(Integer id, Integer userId) throws Exception {
        Connection conn = DBManager.getConn();
        String mainSQL = "select * from message where (user_id = ? or user_id = ?) and  (user1_id = ? or user1_id = ?) ORDER BY create_time\n";
        List<Map<String, Object>> query = qr.query(conn, mainSQL, new MapListHandler(), id, userId,id, userId);
        DbUtils.close(conn);
        return query;
    }

    public List<Map<String, Object>> selectMessageList(Integer id,String name) throws Exception {
        Connection conn = DBManager.getConn();
        try {
            String setRowNumberSQL = "SET @row_number = 0;";
            qr.update(conn, setRowNumberSQL);

            String setCurrentUserSQL = "SET @current_user = NULL;";
            qr.update(conn, setCurrentUserSQL);

            String mainSQL = "SELECT result.user, user_info.user_name, user_info.img,\n" +
                    "result.content, result.create_time FROM ( \n" +
                    "SELECT user, content, create_time, \n" +
                    "@row_number := CASE WHEN @current_user = user THEN @row_number + 1 ELSE 1 END AS rn, \n" +
                    "@current_user := user \n" +
                    "FROM ( SELECT user_id AS user, content, create_time \n" +
                    "FROM ( SELECT user_id, user1_id AS sender_id, content, create_time\n" +
                    "FROM message WHERE user1_id = ? UNION ALL \n" +
                    "SELECT user1_id, user_id AS sender_id, content, create_time \n" +
                    "FROM message WHERE user_id = ? ) AS subquery \n" +
                    "ORDER BY user_id, create_time DESC ) AS ranked_messages \n" +
                    ") AS result JOIN ums_user AS user_info ON user_info.id = result.user \n" +
                    "WHERE user_info.user_name like ? and rn = 1 ORDER BY result.create_time DESC;";

            List<Map<String, Object>> query = qr.query(conn, mainSQL, new MapListHandler(), id, id,"%"+name+"%");
            return query;
        } finally {
            DbUtils.close(conn);
        }
    }

    public boolean insertMessage(Integer id,Integer userId,String content) throws Exception {
        Connection conn = DBManager.getConn();
        String mainSQL = "INSERT INTO `message` VALUES (null, ?, ?, null, ?, ?, ?, NULL, NULL, '0');\n";
        int update = qr.update(conn, mainSQL, userId, content, id,DateUtil.datetime(), userId);
        DbUtils.close(conn);
        return update>0?true:false;
    }

    public List<Map<String, Object>> selectFollowUserMessage(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String mainSQL = "select u.id,user_name,img from ums_user u INNER JOIN (select user_id from follow where user1_id = ? and tombstone = '0' and user_id in (select user1_id from " +
                "follow where user_id = ? and tombstone = '0')) f ON u.id = f.user_id";
        List<Map<String, Object>> query = qr.query(conn, mainSQL, new MapListHandler(), id,id);
        DbUtils.close(conn);
        return query;
    }

    public Long selectFollowUser(Integer id,Integer userId) throws Exception {
        Connection conn = DBManager.getConn();
        String mainSQL = "select count(1) from ums_user u INNER JOIN \n" +
                "(select user_id from follow where user1_id = ? and tombstone = '0' and user_id in \n" +
                "(select user1_id from follow where user_id = ? and tombstone = '0')) f ON u.id = f.user_id where id = ?";
        Long query = qr.query(conn, mainSQL, new ScalarHandler<Long>(), id, id, userId);
        DbUtils.close(conn);
        return query;
    }

    public List<Map<String, Object>> selectFollowUserMessageCount(Integer id,Integer userId) throws Exception {
        Connection conn = DBManager.getConn();
        String mainSQL = "select * from message where (user_id = ? or user_id = ?) and  (user1_id = ? or user1_id = ?) " +
                "ORDER BY create_time desc limit 0,1";
        List<Map<String, Object>> query = qr.query(conn, mainSQL, new MapListHandler(), id,userId,id,userId);
        DbUtils.close(conn);
        return query;
    }
}
